const express = require('express');
const mysql = require('mysql');
const bodyParser = require('body-parser');
const pool = mysql.createPool({
  host: '127.0.0.1',   //MySQL服务器地址
  port: 3306,          //MySQL服务器端口号
  user: 'root',        //数据库用户的用户名
  password: '',        //数据库用户密码
  database: 'zgz',    //数据库名称
  connectionLimit: 20, //最大连接数
  charset: 'utf8',      //数据库服务器的编码方式
  multipleStatements:true//允许一次执行多条sql语句
});
const server = express();
//托管静态资源目录
server.use(express.static('./public'))
//添加中间键
server.use(bodyParser.urlencoded({
  extended: false
}));
const cors = require('cors');
server.use(cors({
  origin: '*'
}));

//注册  测试完成
server.post('/register',(req,res)=>{
  let username=req.body.username
  let nickname=req.body.nickname
  let upwd=req.body.upwd
  let phone=req.body.phone
  let email=req.body.email

  let sql = 'SELECT COUNT(uid) AS count FROM zgz_user WHERE phone=?';
  pool.query(sql,phone,(error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = 'INSERT zgz_user(nickname,upwd,username,phone,email) VALUES(?,?,?,?,?)';
      pool.query(sql, [username,upwd,nickname,phone,email], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200 });
      })
    } else {
      res.send({ message: 'phone exists', code: 201 });
    }
  })		
})

//登录接口 
// 登录 测试完成
server.post('/login',(req,res)=>{
  console.log(req.body)
  let phone=req.body.phone
  let upwd=req.body.upwd

  let sql='select uid,username,upwd,phone,nickname  from zgz_user where phone=? and upwd=?'
  pool.query(sql,[phone,upwd],(error,result)=>{
    if(error) throw error
    console.log(result);
    if(result.length==0){
      res.send({code:201,msg:'login failed'})
    }else{
      res.send({msg:'ok',code:200,result:result[0]});
    }
  })
})
//分页搜索查询
server.get('/sousuo',(req,res)=>{
	let pageSize=7//每页的数量
	let pno=req.query.pno  //当前页码
	let dd = req.query.dd  //查询的商品关键字
	console.log(pageSize)
let sql= `select * from zgz_laptop  where title like + '%${dd}%' ORDER BY spec DESC `
	pool.query(sql,(error,results)=>{
	if(error)throw error
	const pageSize=7
let sumpage=Math.ceil(results.length/pageSize)

          console.log(sumpage)       	//总分页码
     if(pno==undefined){
		 let data=results.splice(0,5)
		 console.log(data)
		 res.send({
		sumpage:1,
        status: 200,
        message: '获取用户信息成功！',
        data: data 
		 })
		 
	 }else{
		  let data=results.splice((pno-1)*pageSize,pageSize)//利用数组方法截取数据
		    res.send({
        sumpage:sumpage,
         status: 201,
         message: '获取用户基本信息成功！',
         data: data,
         pno:Number(pno)		 
     })
		 
	 }		
	})
})

//主页执行多条数据总和接口
server.get('/cha',(req,res)=>{
 let sql1='select * from zgz_laptop  where fid=1 ORDER BY price DESC LIMIT 0,4'
 let sql2='select * from zgz_laptop  where fid=2 ORDER BY price DESC LIMIT 0,4'
let sql3='select * from zgz_laptop  where fid=5 ORDER BY price DESC LIMIT 0,4'
let sql=`${sql1};${sql2};${sql3}`                           
 console.log(sql)
pool.query(sql,(error,results)=>{ 

  if(error){  
    throw error;  
  }else{
     res.send({
	
        status: 200,
        message:'获取信息成功！',
	 res:results
		 })	 
  }  	
})
})
//详情页接口
server.get('/detail',(req,res)=>{
 let lid=req.query.lid
 let sql= `select * from zgz_laptop inner join zgz_laptop_pic on zgz_laptop.lid=zgz_laptop_pic.lid where zgz_laptop.lid=${lid}`
  pool.query(sql,lid,(error,result)=>{
    if(error) throw error
    console.log(result);
    if(result.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:result[0]});
    }
  })
})


server.get('/piao_fy',(req,res)=>{
	let pageSize=7//每页的数量
	let fid=req.query.fid
	let pno=req.query.pno  //当前页码
	console.log(pageSize)
let sql= `select * from zgz_laptop  where fid=${fid} ORDER BY spec DESC `
	pool.query(sql,(error,results)=>{
	if(error)throw error
	const pageSize=7
let sumpage=Math.ceil(results.length/pageSize)

          console.log(sumpage)       	//总分页码
     if(pno==undefined){
		 let data=results.splice(0,5)
		 console.log(data)
		 res.send({
		sumpage:1,
        status: 200,
        message: '获取用户信息成功！',
        data: data 
		 })		 
	 }else{
		  let data=results.splice((pno-1)*pageSize,pageSize)//利用数组方法截取数据
		    res.send({
        sumpage:sumpage,
         status: 201,
         message: '获取用户基本信息成功！',
         data: data,
         pno:Number(pno)		 
     })		 
	 }		
	})
})

//表类别查询
server.get('/piao',(req,res)=>{
  let lid = req.query.lid
  let sql =` select * from zgz_laptop where fid=${lid}`
  pool.query(sql,(error,results)=>{
    if(error) throw error
   if(results.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:results});
    }
  })
})

//已买订单状态查询接口
server.get('/dd_cha',(req,res)=>{
  let uid=req.query.uid
  let sql= `select * from zgz_pay_info inner join zgz_laptop on zgz_pay_info.order_id=zgz_laptop.lid where zgz_pay_info.uid=${uid}`
 
  pool.query(sql,uid,(error,result)=>{
    if(error) throw error
  
    if(result.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:result});
    }
  })
})

//订单添加接口
server.post('/Addzz',(req,res)=>{
  let uid=req.body.uid
  let lid=req.body.lid
  let user_address=req.body.user_address
  let status=req.body.status
  let order_time=req.body.order_time
  let pay_time=req.body.pay_time
  let deliver_time=req.body.deliver_time
  let received=req.body.received
  let title=req.body.title
  let price=req.body.price
  let pic=req.body.pic
  let num=req.body.num
  let category=req.body.category
  let freight=req.body.freight
  let paylei=req.body.paylei
  let emailname=req.body.emailname
  let tel=req.body.tel
 // 将用户的相关信息插入到订单数据表
      sql = 'INSERT zgz_order(uid,lid,user_address,status,order_time,pay_time,deliver_time,received,title,price,pic,num,category,freight,paylei,emailname,tel) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
      pool.query(sql, [uid,lid,user_address,status,order_time,pay_time,deliver_time,received,title,price,pic,num,category,freight,paylei,emailname,tel], (error, results) => {
        if (error) throw error;
       else{
		   res.send({msg:'ok',code:200})
	   }
})
})

//查询当前登录人的默认收货地址

server.get('/shouhuoCha',(req,res)=>{
  let uid=req.query.uid
  let sql= `select * from zgz_address where uid=${uid} and pid=1`
 
  pool.query(sql,[uid],(error,result)=>{
    if(error) throw error
  
    if(result.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:result});
    }
  })
})
 


//订单查询 
server.get('/ddCha',(req,res)=>{
  let uid=req.query.uid
   let status=req.query.status
  let sql= `select * from zgz_order where uid=${uid} and status=${status}`
 
  pool.query(sql,[uid,status],(error,result)=>{
    if(error) throw error
  
    if(result.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:result});
    }
  })
})
 //订单详情查询 
server.get('/ddxqCha',(req,res)=>{
  let uid=req.query.uid
   let oid=req.query.oid
  let sql= `select * from zgz_order where uid=${uid} and oid=${oid}`
 
  pool.query(sql,[uid,oid],(error,result)=>{
    if(error) throw error
  
    if(result.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:result});
    }
  })
})
 
//订单删除接口
server.get('/dd_sc', (req, res, next) => {
  let oid=req.query.oid
  let sql = 'delete from zgz_order where oid=?'
  pool.query(sql,oid,(error, results) => {
    if (error) {
      next(error)
      return
    }
    if (results.affectedRows == 0) {
      res.send({ message: 'fail to delete ', code: 201 })
    } else {
      res.send({ message: 'successfully delete', code: 200 })
    }
  })
})

//收藏删除接口
server.get('/scc_sc', (req, res, next) => {
  let lid=req.query.lid
  let sql = 'delete from zgz_collect where lid=?'
  pool.query(sql,lid,(error, results) => {
    if (error) {
      next(error)
      return
    }
    if (results.affectedRows == 0) {
      res.send({ message: 'fail to delete ', code: 201 })
    } else {
      res.send({ message: 'successfully delete', code: 200 })
    }
  })
})

//收藏添加接口
server.post('/Addcollect',(req,res)=>{
  let uid=req.body.uid
  let lidd=req.body.lidd
  let title=req.body.title
  let price=req.body.price
  let pic=req.body.pic
  let sql = 'SELECT COUNT(lidd) AS count FROM zgz_collect WHERE uid=? and lidd=?';
  pool.query(sql,[uid,lidd],(error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
         sql = 'INSERT zgz_collect(uid,title,price,pic,lidd) VALUES(?,?,?,?,?)';
      pool.query(sql, [uid,title,price,pic,lidd], (error, results) => {
        if (error) throw error;
       else{
		   res.send({msg:'ok',code:200})
	   }
})
    } else {
      res.send({ message: 'collect exists', code: 201 });
    }
  })		
})

//收藏查询 
server.get('/sccha',(req,res)=>{
  let uid=req.query.uid
  let sql= 'select * from zgz_collect where uid=?'
 
  pool.query(sql,uid,(error,result)=>{
    if(error) throw error
  
    if(result.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:result});
    }
  })
})
 
 //购物车添加接口
server.post('/Addmallcart',(req,res)=>{
  let uid=req.body.uid
  let lidd=req.body.lidd
  let title=req.body.title
  let price=req.body.price
  let pic=req.body.pic
  let num=req.body.num
  let sql = 'INSERT zgz_mall_cart(uid,title,price,pic,lidd,num) VALUES(?,?,?,?,?,?)';
      pool.query(sql, [uid,title,price,pic,lidd,num], (error, results) => {
        if (error) throw error;
       else{
		   res.send({msg:'ok',code:200})
	   }
})

})
 
 //收藏查询 
server.get('/Addmallcartcha',(req,res)=>{
  let uid=req.query.uid
  let sql= 'select * from zgz_mall_cart where uid=?'
 
  pool.query(sql,uid,(error,result)=>{
    if(error) throw error
  
    if(result.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:result});
    }
  })
})
 
 
 //足迹查询 
server.get('/zjcha',(req,res)=>{
  let uid=req.query.uid
  let sql= 'select * from zgz_footstep where uid=?'
 
  pool.query(sql,uid,(error,result)=>{
    if(error) throw error
  
    if(result.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:result});
    }
  })
})
 

//足迹添加 测试完成
server.post('/addfooter',(req,res)=>{
  let uid=req.body.uid
  let lidd=req.body.lidd
  let title=req.body.title
  let price=req.body.price
  let pic=req.body.pic
  let sql = 'SELECT COUNT(lid) AS count FROM zgz_footstep WHERE uid=? and lidd=?';
  pool.query(sql,[uid,lidd],(error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
         sql = 'INSERT zgz_footstep(uid,title,price,pic,lidd) VALUES(?,?,?,?,?)';
      pool.query(sql, [uid,title,price,pic,lidd], (error, results) => {
        if (error) throw error;
       else{
		   res.send({msg:'ok',code:200})
	   }
})
    } else {
      res.send({ message: 'footer exists', code: 201 });
    }
  })		
})

//退款同意接口
server.post('/tytk',(req,res)=>{
  let uid=req.body.uid
  let oid=req.body.oid
 // 将用户的相关信息插入到订单数据表
      sql = `UPDATE zgz_order set status=4 WHERE uid=${uid} and oid='${oid}'`;
      pool.query(sql, [uid,oid], (error, results) => {
        if (error) throw error;
       else{
		   res.send({msg:'ok',code:200})
	   }
})
})


//催发货接口
server.post('/speedfh',(req,res)=>{
  let uid=req.body.uid
  let oid=req.body.oid
 // 将用户的相关信息插入到订单数据表
      sql = `UPDATE zgz_order set status=3 WHERE uid=${uid} and oid='${oid}'`;
      pool.query(sql, [uid,oid], (error, results) => {
        if (error) throw error;
       else{
		   res.send({msg:'ok',code:200})
	   }
})
})
//收货地址添加接口
server.post('/ssdztj',(req,res)=>{
  let uid=req.body.uid
  let address=req.body.address
  let name=req.body.name
  let tel=req.body.tel
  let province=req.body.province
  let county=req.body.county
  let diqutel=req.body.diqutel
  let pid=req.body.pid
  let tell=req.body.tell
  let pidd='1'
  let sql = `SELECT COUNT(uid) AS count FROM zgz_address WHERE pid=${pidd} and uid=${uid}`;
  
  console.log(sql)
  pool.query(sql,(error, results) => {
    if (error) throw error;
    let count = results[0].count;
	console.log(count)
    if (count == 0) {
         sql = 'INSERT zgz_address(uid,address,name,tel,province,county,diqutel,pid,tell) VALUES(?,?,?,?,?,?,?,?,?)';
      pool.query(sql, [uid,address,name,tel,province,county,diqutel,pid,tell], (error, results) => {
        if (error) throw error;
       else{
		   res.send({msg:'ok',code:200})
	   }
})
    } else {
      res.send({ message: '收获地址已存在', code: 201 });
    }
  })
})


//收货地址添加接口2
server.post('/ssdztj2',(req,res)=>{
  let uid=req.body.uid
  let address=req.body.address
  let name=req.body.name
  let tel=req.body.tel
  let province=req.body.province
  let county=req.body.county
  let diqutel=req.body.diqutel
  let pid=req.body.pid
  let tell=req.body.tell
    let pidd='1'
  let sql = `SELECT COUNT(pid) AS count FROM zgz_address WHERE pid=${pidd} and uid=${uid}`;
  
  console.log(sql)
  pool.query(sql,(error, results) => {
    if (error) throw error;
    let count = results[0].count;
	console.log(count)
    if (count == 0 || count==1) {
         sql = 'INSERT zgz_address(uid,address,name,tel,province,county,diqutel,pid,tell) VALUES(?,?,?,?,?,?,?,?,?)';
      pool.query(sql, [uid,address,name,tel,province,county,diqutel,pid,tell], (error, results) => {
        if (error) throw error;
       else{
		   res.send({msg:'ok',code:200})
	   }
})
    } else {
      res.send({ message: '默认收获地址已存在', code: 201 });
    }
  })
})





//查询收货地址是否存在以及pid
server.get('/shczcha',(req,res)=>{
  let uid=req.query.uid
  let sql= `select * from zgz_address where uid=? ORDER BY pid DESC LIMIT 0,1`
 
  pool.query(sql,uid,(error,result)=>{
    if(error) throw error
  
    if(result.length==0){
      res.send({code:404,msg:'not found',result:'0'})
    }else{
      res.send({msg:'ok',code:200,result:result});
    }
  })
})



//查询收货地址
server.get('/chaAddress',(req,res)=>{
  let uid = req.query.uid
  let sql ='select * from zgz_address where uid=?'
  pool.query(sql,uid,(error,results)=>{
    if(error) throw error
   if(results.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:results});
    }
  })
})

//查询收货地址
server.get('/xhaAddress',(req,res)=>{
  let uid = req.query.uid
 let id = req.query.id
  let sql =`select * from zgz_address where uid=${uid} and id=${id}`
  pool.query(sql,uid,(error,results)=>{
    if(error) throw error
   if(results.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:results});
    }
  })
})


server.listen(3000, () => {
  console.log('服务器成功!!!');
});